Databases and Structured Data

If you have ever worked with or created databases, then you understand the advantages of structured data.  You are also familiar with the concept of content modeling, even though the term may be new to you.  

When you develop a database using a relational database product (e.g. SQL Server, Oracle, MS Access, etc.), you model the information that you are going to store using tables and relationships (primary and foreign keys).  You are creating a defined structure that the content contained within the database must adhere to.

Let’s take a look at a typical relational database structure for storing employee information.  We are going to stick with the model that we have worked with in our example employee XML document.  While this may not be the optimal model according to relational database theory, this is a simple example and will suite our needs.  

Using the relational model, our content model can be broken into three tables: “employees”, “phonenumbers” and “phonetypes”.  

Tip:	The example database that we are using is a Microsoft Access database.  You can view the contents using Microsoft Access 97 or higher.

The following diagram shows the table structures and the relationships between them.

File: Chapter 4\Employees.mdb in Microsoft Access (Relationship View)

The following is a breakdown of the tables and the relationships between them.

Employees Table

The “employees” table has 8 distinct columns (or elements).

id – 	unique ID for the employee.  Since this column is our primary 
	key, we are guaranteed that it will be unique.
firstname – the first name of the employee.
lastname – the last name of the employee.
position – the role of the employee in the organization.
street – address street.
city – address city.
state – address state.
zip – address zip.

Phone Types Table

The “phonetypes” table only has one column (element).

phonetype – 	this is the type of phone number (e.g. main, fax, home, 
	etc.).  This column is a primary key so it is guaranteed 
	to be unique.

Phone Numbers Table

The “phonenumbers” table has three columns (elements).

id – 	this column maps to the “id” column in the “employees” table in 
	a foreign key relationship (there can be multiple phone numbers 
	for a single employee).  It is also part of a compound primary 
	key with the “phonetype” column, and we will explain that next.

phonetype – 	this column maps to the phonetype column in the 
	phonetypes table in a foreign key relationship (there 
	can be multiple types of phone numbers for a single 
	employee).

This column in association with the “id” column make the primary key for this table.  This compound primary key make it impossible to have two phone numbers of the same type.  While this may not be advantages in the real world, it suites our example.

phonenumber – this column contains an instance of a phone number 
	for an employee.

Let’s add a few more rules to our content model that will make our data valuable.  Let’s make some columns required (data must exist in them), while others are optional.

Remember, fields that are defined as primary and foreign keys are already required columns as a rule of relational database theory.

Employees Table

Required: id, firstname, lastname, position
Optional: street, city, state, zip

Phone Types Table

Required: phonetype

Phone Numbers Table

Required: id, phonetype, phonenumber

We have now created a relational database (entity) that models the content that we want to store in it.  We have specified the columns (elements) that are contained within the database, and we have defined the rules for required and optional content.

If you are working against a server database such as Microsoft SQL Server, ORACLE, or DB2, instead of using an interactive environment like Microsoft Access to create a database, you might create a SQL (Structured Query Language) script.  The following script works with Microsoft SQL Server 7.0 and higher.

CREATE TABLE employees
(
	id			VARCHAR(50)	NOT NULL,
	firstname	VARCHAR(50)	NOT NULL,
	lastname		VARCHAR(50)	NOT NULL,
	street		VARCHAR(50)	NULL,
	city		VARCHAR(50)	NULL,
	state		VARCHAR(50)	NULL,
	zip		VARCHAR(50)	NULL,

	CONSTRAINT pk_employees PRIMARY KEY 
		CLUSTERED ( id )
)
GO

CREATE TABLE phonetypes
(
	phonetype	VARCHAR(50)	NOT NULL,

	CONSTRAINT pk_phonetypes PRIMARY KEY 
		CLUSTERED ( phonetype )
)
GO

CREATE TABLE phonenumbers
(
	id			VARCHAR(50)	NOT NULL,
	phonetype	VARCHAR(50)	NOT NULL,
	phonenumber	VARCHAR(50)	NOT NULL,

	CONSTRAINT pk_phonenumbers PRIMARY KEY 
		CLUSTERED ( id, phonetype ),

	CONSTRAINT fk_phonenumbers1 
		FOREIGN KEY ( id )
		REFERENCES employees ( id ),

	CONSTRAINT fk_phonenumbers2 
		FOREIGN KEY ( phonetype )
		REFERENCES phonetypes ( phonetype )
)
GO
File: Chapter 4\Employees.sql

While the syntax and development environments may change between database products, the end results are the same.  A database storage object (entity) is created that has defined rules that comprise the content model.

The advantages of using a relational database are many.  A few of the advantages are:

The structure inherently protects us from invalid data.
We can develop software to access the database provided we know the structure of the content model.
We can query the database.
We can query the content model.

The biggest downside is that our database is not geared for use on the Internet or via Internet protocols (HTTP, FTP, etc.).  Second, in the case of our Microsoft Access database, it is specific to the Microsoft Windows platform.  

This is where XML comes in to play.  XML is geared towards working with Internet technologies and is platform independent.  What’s even better is that we do not lose the advantages that we got when we developed our content model using a relational database.  In XML, we can control the content model, just like we did in Access and SQL Server.